﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'fn_ContainsTier'))
DROP FUNCTION [fn_ContainsTier]
GO
-- Return bit value indicating that whether the TargetTier contains Tier
CREATE FUNCTION [fn_ContainsTier](
	@TargetTierKey [uniqueidentifier],
	@TierKey [uniqueidentifier]
)
RETURNS BIT

AS
BEGIN
	DECLARE @Result AS BIT;
	DECLARE @CurrentTierValue AS INT;
	DECLARE @TopTierValue AS INT;

	SET @Result = 0;
	IF(@TargetTierKey IS NULL)
		SET @Result = 1;
	ELSE IF(@TargetTierKey IS NOT NULL AND @TierKey IS NOT NULL)
	BEGIN
		WITH [CTE] AS (
					SELECT 
						G.[Key]
						,G.[ParentKey]
						,G.[TierValue]
						,G.[Name] 
					FROM [dbo].[GeographyTier] AS G where G.[ParentKey] = @TargetTierKey
					UNION ALL
					SELECT T.[Key]
						,T.[ParentKey]
						,T.[TierValue]
						,T.[Name] 
						 FROM [CTE] AS C, [GeographyTier] AS T where T.[ParentKey] = C.[Key]
				)
				SELECT @Result = 1   FROM [CTE] WHERE [Key] = @TierKey;
	END
	
	return @Result;
END
GO